/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Project2.cs342.project1.mathNotebook.Database;

import java.sql.*;
import java.io.*;
import java.math.BigInteger;
import java.security.*;
import javax.crypto.SecretKey;
import java.lang.*;


/**
 *
 * @author Rick
 */
public class DBNotebooks {

    static String id = null;
    static String file_name = null;
    static Connection conn = null;
    PreparedStatement ps = null;
    SQLConfigeration sqlConfig;

    @SuppressWarnings("static-access")
    public DBNotebooks(){
        sqlConfig = new SQLConfigeration();
        conn = sqlConfig.getConnection();
    }

    /**
     * Inserts a given file into the table
     *
     * @param filename the name of the file to be inserted
     * @param date the date of insertion
     * @param lastModifiedBy date of last modification
     * @param rsaPublicKey the public rsa key located on the cloud
     * @param rsaPrivateKey the private rsa key located on the cloud
     * @param desKey the des key located on the clous
     * @param lockedBy the userID of the user who currently has this file locked
     */
    public void insertIntoNotebooks(
                        String filename, 
                         String userName, byte[] publicKey,
                         byte[] privateKey
                        ,BigInteger desKey, String group){

        //Insert data
        System.out.println("desKey: " + desKey);
        String InsertEncrypt = "insert into Notebooks(filename, contents," +
                                                    "cloudPublicKey, cloudPrivateKey," +
                                                     "encryptedDES)" +
                                                     "values" +
                                                     "(?, ?, ?, ?, ?)";
        FileInputStream fis = null;
        //System.out.println("Encrypted DES: " + desKey.toString().length());
        try{
            conn.setAutoCommit(false);
            File fileNotebook = new File(filename);
            if(!fileNotebook.exists())
                fileNotebook.createNewFile();
            File filePublicKey = new File("public.txt");
            if(!filePublicKey.exists())
                filePublicKey.createNewFile();
            File filePrivateKey = new File("private.txt");
            if(!filePrivateKey.exists())
                filePrivateKey.createNewFile();
            File fileCloudDES = new File("databaseDes.txt");
            if(!fileCloudDES.exists())
                fileCloudDES.createNewFile();
            
            ps = conn.prepareStatement(InsertEncrypt);

            ps.setString(1, filename);
            fis = new FileInputStream(fileNotebook);
            ps.setBinaryStream(2, fis, (int) fileNotebook.length());
            
            fis = new FileInputStream(filePublicKey);
            ps.setBinaryStream(3, fis, (int)filePublicKey.length());
            fis = new FileInputStream(filePrivateKey);
            ps.setBinaryStream(4, fis, (int)filePrivateKey.length());
            fis = new FileInputStream(fileCloudDES);
            ps.setBinaryStream(5, fis, (int)fileCloudDES.length());
            System.out.println("Attempting insert");
            ps.executeUpdate();
            conn.commit();
            System.out.println("Insert successful");
            DBGroupAccess groupAccess = new DBGroupAccess();
            groupAccess.insertIntoGroupAccess(group, filename);

        }catch(Exception ex){
            System.out.println("Insert Error " + ex + " at:");
            ex.printStackTrace();
        }finally{
            try {
                ps.close();
                fis.close();
            } catch (SQLException ex) {
                System.out.println("Sql Error " + ex);
            } catch(IOException ex){
                System.out.println("IO Error " + ex);
            }
        }

    }

    /**
     * Receives the notebooks contents and writes it out to a file
     *
     * @param filename the name of the file to be received
     */
    public void retrieveNotebooksContents(String filename, String notebook){
        System.out.println("Started Function");
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select * from Notebooks where filename = ?";


        Blob blob = null;

        try{
            pstmt = conn.prepareStatement(query);
//            pstmt.setString(1, "*");
            pstmt.setString(1, notebook);
            rs = pstmt.executeQuery();
            rs.next();
            System.out.println("Second Try: " + rs.getString("filename"));
        //System.out.println("Encrypted DES: " + desKey.toString().length());

            conn.setAutoCommit(false);
            File fileNotebook = new File(filename);
            if(!fileNotebook.exists())
                fileNotebook.createNewFile();
            File filePublicKey = new File("public.txt");
            if(!filePublicKey.exists())
                filePublicKey.createNewFile();
            File filePrivateKey = new File("private.txt");
            if(!filePrivateKey.exists())
                filePrivateKey.createNewFile();
            File fileCloudDES = new File("databaseDes.txt");
            if(!fileCloudDES.exists())
                fileCloudDES.createNewFile();
            
            
            System.out.println("Attempting to get the blob");
            blob = rs.getBlob("contents");

            System.out.println("Blob recieved of length " + blob.length());
            
            FileOutputStream  outStream  = new FileOutputStream(fileNotebook); 
            InputStream       bis   = blob.getBinaryStream();
            ByteArrayOutputStream bo = new ByteArrayOutputStream();
            byte[] buf = new byte[4096];
            int n = 0;

            while ((n = bis.read(buf, 0, 4096)) != -1) {
            
            outStream.write(buf, 0, n);
            }
            bo.flush();
            blob = rs.getBlob("cloudPublicKey");
            outStream  = new FileOutputStream(filePublicKey);
            bis   = blob.getBinaryStream();
            bo = new ByteArrayOutputStream();
            buf = new byte[4096];
            n = 0;

            while ((n = bis.read(buf, 0, 4096)) != -1) {

            outStream.write(buf, 0, n);
            }
            bo.flush();
            blob = rs.getBlob("cloudPrivateKey");
            outStream  = new FileOutputStream(filePrivateKey);
            bis   = blob.getBinaryStream();
            bo = new ByteArrayOutputStream();
            buf = new byte[4096];
            n = 0;

            while ((n = bis.read(buf, 0, 4096)) != -1) {

            outStream.write(buf, 0, n);
            }
            bo.flush();
            blob = rs.getBlob("encryptedDES");
            outStream  = new FileOutputStream(fileCloudDES);
            bis   = blob.getBinaryStream();
            bo = new ByteArrayOutputStream();
            buf = new byte[4096];
            n = 0;

            while ((n = bis.read(buf, 0, 4096)) != -1) {

            outStream.write(buf, 0, n);
            }

            bo.flush();
            bo.close();
            bis.close();
//            ps.setBinaryStream(2, fis, (int) fileNotebook.length());
//
//            fis = new FileInputStream(filePublicKey);
//            ps.setBinaryStream(3, fis, (int)filePublicKey.length());
//            fis = new FileInputStream(filePrivateKey);
//            ps.setBinaryStream(4, fis, (int)filePrivateKey.length());
//            fis = new FileInputStream(fileCloudDES);
//            ps.setBinaryStream(5, fis, (int)fileCloudDES.length());
            System.out.println("Finished Download");

            //get the binary blob data onto the client
            

        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("Sql Error " + ex);


        }

        //writing out the recieved blob
//        try{
//            FileOutputStream out = new FileOutputStream(filename);
//            BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());
//
//            int b;
//            byte[] buffer = new byte[10240];
//            try{
//                while((b = in.read(buffer, 0, 10240)) != -1){
//                    out.write(buffer, 0, b);
//                }
//                out.close();
//            }catch(IOException ex){
//                System.out.println("File Output Error: " + ex);
//            }
//
//        }catch(FileNotFoundException ex){
//            System.out.println("Error: " + ex);
//        }catch(SQLException ex){
//            System.out.println("SQL Error: " + ex);
//        }
    }
    }

    /**
     * retrieves the lastmodified date of the provided file
     *
     * @param filename name of the modified file
     * @return date of last modification
     */
    public String retrieveLastModifiedDate(String filename){
        String date = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select lastModifiedDate from Notebooks where filename = ?";

        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, filename);
            rs = pstmt.executeQuery();


            System.out.println("Attempting to get the Files");

            date = rs.getString("lastModifiedDate");


        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }
        }

        return date;
    }

    /**
     * retrieves the name of the user who last modified this file
     *
     * @param filename the name of the modified file
     * @return the name of the user who last modified the file
     */
    public String retrieveLastModifiedBy(String filename){
        String username = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select lastModifiedBy from Notebooks where filename = ?";

        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, filename);
            rs = pstmt.executeQuery();


            System.out.println("Attempting to get the Files");

            username = rs.getString("lastModifiedBy");

        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }
        }
        return username;
    }

     /**
     * retrieves the rsa public key for a file
     *
     * @param filename the filename for which the key is associated to
     * @return the rsa public key in the form of a byte array
     */
    public void retrieveCloudRSAPublicKey(String filename){
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select cloudPublicKey from Notebooks where filename = ?";
        Blob blob = null;

        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, filename);
            rs = pstmt.executeQuery();

            rs.next();

            //get the binary blob data onto the client
            System.out.println("Attempting to get the blob");
            blob = rs.getBlob(1);
            System.out.println("Blob recieved of length " + blob.length());

        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }

        }

        //writing out the recieved blob
        try{
            FileOutputStream out = new FileOutputStream("public.txt");
            BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());

            int b;
            byte[] buffer = new byte[10240];
            try{
                while((b = in.read(buffer, 0, 10240)) != -1){
                    out.write(buffer, 0, b);
                }
                out.close();
            }catch(IOException ex){
                System.out.println("File Output Error: " + ex);
            }

        }catch(FileNotFoundException ex){
            System.out.println("Error: " + ex);
        }catch(SQLException ex){
            System.out.println("SQL Error: " + ex);
        }
    }

    /**
     * retrieves the rsa Private key for a given file
     *
     * @param filename the file the key is associated to
     * @return the rsa Private Key in the form of a byte array
     */
    public void retrieveCloudRSAPrivateKey(String filename){
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select cloudPrivateKey from Notebooks where filename = ?";
        Blob blob = null;

        try {
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, filename);
            rs = pstmt.executeQuery();

            rs.next();

            //get the binary blob data onto the client
            System.out.println("Attempting to get the blob");
            blob = rs.getBlob(1);
            System.out.println("Blob recieved of length " + blob.length());

        } catch (Exception ex) {
            System.out.println("Retrieval Error: " + ex);
        } finally {
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }

        }

        //writing out the recieved blob
        try {
            FileOutputStream out = new FileOutputStream("private.txt");
            BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());

            int b;
            byte[] buffer = new byte[10240];
            try {
                while ((b = in.read(buffer, 0, 10240)) != -1) {
                    out.write(buffer, 0, b);
                }
                out.close();
            } catch (IOException ex) {
                System.out.println("File Output Error: " + ex);
            }

        } catch (FileNotFoundException ex) {
            System.out.println("Error: " + ex);
        } catch (SQLException ex) {
            System.out.println("SQL Error: " + ex);
        }
    }

    /**
     * retrieve the des key in the form of a bigInteger
     *
     * @param filename the filename associated with this desKey
     * @return a bigInteger representing the desKey
     */
    public void retrieveDESKey(String filename){

        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select encryptedDES from Notebooks where filename = ?";
        Blob blob = null;

        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, filename);
            rs = pstmt.executeQuery();

            rs.next();

            //get the binary blob data onto the client
            System.out.println("Attempting to get the blob");
            blob = rs.getBlob(1);
            System.out.println("Blob recieved of length " + blob.length());

        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }

        }

        //writing out the recieved blob
        try{
            FileOutputStream out = new FileOutputStream("databaseDes.txt");
            BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());

            int b;
            byte[] buffer = new byte[10240];
            try{
                while((b = in.read(buffer, 0, 10240)) != -1){
                    out.write(buffer, 0, b);
                }
                out.close();
            }catch(IOException ex){
                System.out.println("File Output Error: " + ex);
            }

        }catch(FileNotFoundException ex){
            System.out.println("Error: " + ex);
        }catch(SQLException ex){
            System.out.println("SQL Error: " + ex);
        }
    }

    /**
     * retrieves the userID of the user who currently has a lock on the file
     *
     * @param filename the name of the locked file
     * @return the userID of the user who locked the file
     */
    public String retrieveUserWhoLockedFile(String filename){
        String userID = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select lockedBy from Notebooks where filename = ?";

        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, filename);
            rs = pstmt.executeQuery();


            System.out.println("Attempting to get the Files");

            userID = rs.getString("lockedBy");


        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                //conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }
        }
        return userID;
    }

    /**
     * Deletes a given file from the table
     *
     * @param filename the name of the file to be deleted
     */
    public void deleteNotebookFile(String filename){
        String deleteNotebook = "delete from Notebooks where filename = '" + filename + "'";
         String deleteNotebook2 = "delete from GroupAccess where filename = '" + filename + "'";
        try{
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(deleteNotebook);

            System.out.println("Attempting Deletion");
            ps.executeUpdate();
            ps = conn.prepareStatement(deleteNotebook2);
            ps.executeUpdate();
            conn.commit();
            System.out.println("Delection Successful");

        }catch(SQLException ex){
            System.out.println("Deletion Error: " + ex);
        }

    }
}
